﻿-- IDENTIFIER SEQUENCE
CREATE SEQUENCE "STOCK_TALLY_ID_SEQ" START WITH 1 INCREMENT BY 1;

-- STOCK TALLY SHEET
CREATE TABLE "STOCK_TALLY" (
	"ID"  NUMERIC(20,0) NOT NULL DEFAULT nextval('"STOCK_TALLY_ID_SEQ"'), -- IDENTIFIER SEQUENCE
	"HEALTH_FACILITY_ID" INTEGER NOT NULL, -- THE IDENTIFIER OF THE HEALTH FACILITY TO WHICH THE STOCK TALLY SHEET APPLIES
	"DOSE_ID" INTEGER NOT NULL, -- THE DOSE FOR WHICH THE TALLY REPRESENTS
	"GENDER" CHAR(1) NOT NULL CHECK ("GENDER" IN ('M','F')), -- THE GENDER
	"ENTRY_DATE" DATE NOT NULL DEFAULT CURRENT_DATE, -- THE DATE OF THE IMPORT
	"MONTH_NO" INTEGER NOT NULL CHECK ("MONTH_NO" BETWEEN 1 AND 12), -- THE MONTH NUMBER OF THE IMPORT
	"COUNT" INTEGER NOT NULL, -- THE COUNT OF THE TALLY
	CONSTRAINT "PK_STOCK_TALLY" PRIMARY KEY ("ID"),
	CONSTRAINT "FK_STOCK_TALLY_HEALTH_FACILITY" FOREIGN KEY ("HEALTH_FACILITY_ID") REFERENCES "HEALTH_FACILITY"("ID"),
	CONSTRAINT "FK_STOCK_TALLY_DOSE" FOREIGN KEY ("DOSE_ID") REFERENCES "DOSE"("ID")
);

CREATE OR REPLACE FUNCTION insert_stock_tally 
(
	HF_IN INTEGER, 
	MONTH_IN INTEGER,
	GENDER_IN CHAR,
	DOSE_ID_IN INTEGER, 
	COUNT_IN INTEGER
) RETURNS VOID AS 
$$
BEGIN

	INSERT INTO "STOCK_TALLY" ("HEALTH_FACILITY_ID", "DOSE_ID", "GENDER", "MONTH_NO", "COUNT") VALUES
		(HF_IN, DOSE_ID_IN, GENDER_IN, MONTH_IN, COUNT_IN);

END;
$$ LANGUAGE PLPGSQL;

